package com.qf.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qf.pojo.DtsOrder;
import com.qf.vo.CategorySellAmts;
import com.qf.vo.DayStatis;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单表 Mapper 接口
 * </p>
 *
 * @author zhaojian
 * @since 2021-05-12
 */
public interface DtsOrderMapper extends BaseMapper<DtsOrder> {
    /**
     * 按天统计订单的增长量和购买金额
     * @param daysAgo
     * @return
     */
    @Select("SELECT  DATE_FORMAT(o.add_time,\"%Y-%m-%d\") AS dayStr,COUNT(*) AS cnts ,SUM(o.`actual_price`) AS amts " +
        "FROM dts_order o " +
        "WHERE o.`deleted`= 0 AND DATE_ADD(NOW(),INTERVAL -#{daysAgo} DAY) AND o.`order_status` NOT IN(101,102,103) " +
        "GROUP BY DATE_FORMAT(o.add_time,\"%Y-%m-%d\") ")
public List<DayStatis> statuisIncrOrderCnt(@Param("daysAgo") Integer daysAgo);
@Select("SELECT c.`name`,SUM(o.`actual_price`) AS value FROM dts_category c " +
        "LEFT JOIN dts_goods g ON g.`category_id`=c.`id` " +
        "LEFT JOIN dts_order_goods og ON og.`goods_id`=g.`id` " +
        "LEFT JOIN dts_order o ON o.`id`=og.`order_id` " +
        "WHERE og.`deleted`=0 AND o.`order_status` NOT IN(101,102,103) " +
        "GROUP BY c.`name`  ")
public  List<CategorySellAmts> categorySellStatis();
@Select("SELECT COUNT(DISTINCT user_id)AS customers,SUM(actual_price)AS amount,COUNT(id)AS orders,SUM(actual_price)/COUNT(id) AS pcr,  " +
        "        DATE_FORMAT(add_time,\"%Y-%m-%d\")  day  FROM dts_order WHERE deleted=0 GROUP BY day " +
        "        ")
List<Map> queryOrderStat();
}
